from sqlalchemy import CheckConstraint

from app.extensions import db
from datetime import datetime


class HouseReviews(db.Model):
    __tablename__ = 'house_reviews'

    #主键（自动递增）
    reviewid=db.Column(
        db.Integer,
        primary_key=True,
        autoincrement=True,
        info={'verbose_name':'评论ID'}
    )

    #外键：关联房源（对应house_info表的houseid）
    houseid=db.Column(
        db.Integer,
        db.ForeignKey('houseinfo.houseid',ondelete='RESTRICT'),
        nullable=False,
        info={'verbose_name':'关联房源'}
    )

    # 外键：关联用户（对应users表的userid）
    userid = db.Column(
        db.Integer,
        db.ForeignKey('users.userid', ondelete='RESTRICT'),
        nullable=False,
        info={'verbose_name': '评价用户'}
    )

    #建立关系
    house=db.relationship(
        'HouseInfo',
        backref=db.backref('reviews', lazy=True),
    )
    user = db.relationship(
        'User',
        backref=db.backref('house_reviews', lazy=True),
    )

    #评分字段（1-5分）
    cleanliness_rating=db.Column(
        db.SmallInteger,
        nullable=False,
        info={'verbose_name':'清洁度评分','help_text':'1-5分'}
    )

    location_rating = db.Column(
        db.SmallInteger,
        nullable=False,
        info={'verbose_name': '位置评分', 'help_text': '1-5分'}
    )

    facility_rating = db.Column(
        db.SmallInteger,
        nullable=False,
        info={'verbose_name': '设施评分', 'help_text': '1-5分'}
    )

    value_rating = db.Column(
        db.SmallInteger,
        nullable=False,
        info={'verbose_name': '性价比评分', 'help_text': '1-5分'}
    )

    communication_rating = db.Column(
        db.SmallInteger,
        nullable=False,
        info={'verbose_name': '房东沟通评分', 'help_text': '1-5分'}
    )

    #评论文本
    comment=db.Column(
        db.Text,
        nullable=True,
        info={'verbose_name':'详细评价'}
    )

    #评价时间（自动记录创建时间）
    review_time=db.Column(
        db.DateTime,
        default=datetime.utcnow,
        nullable=False,
        info={'verbose_name':'评论时间'}
    )

    #表级约束-确保评分在1-5之间
    __table_args__ = (
        CheckConstraint('cleanliness_rating BETWEEN 1 AND 5', name='check_cleanliness_rating'),
        CheckConstraint('location_rating BETWEEN 1 AND 5', name='check_location_rating'),
        CheckConstraint('facility_rating BETWEEN 1 AND 5', name='check_facility_rating'),
        CheckConstraint('value_rating BETWEEN 1 AND 5', name='check_value_rating'),
        CheckConstraint('communication_rating BETWEEN 1 AND 5', name='check_communication_rating'),
        db.Index('houseid_idx', 'houseid'),
        db.Index('userid_idx', 'userid'),
    )

    def __repr__(self):
        return f"<HouseReview {self.reviewid}>"


    def __str__(self):
        return f"{self.house}的评价-{self.user}"
    @property
    def average_rating(self):
        #计算平均分
        ratings=[
            self.cleanliness_rating,
            self.location_rating,
            self.facility_rating,
            self.value_rating,
            self.communication_rating,
        ]
        return round(sum(ratings)/len(ratings),1)